﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using BestBuy.Common;

namespace BestBuy.DAL
{
    public class CProduct
    {
         private TradeKeyDataContext context = new TradeKeyDataContext();
        private SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["TradeKeyConnectionString"].ConnectionString);
        public CProduct()
        { }

        public bool UpdateStatus(int idsanpham,int status)
        {
            var result = (from dl in context.SanPhams
                          where dl.IDSanPham == idsanpham
                          select dl).FirstOrDefault();
            result.TinhTrang = status;
            try
            {
                context.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public int soLuongDuLieu(int status)
        {
            sqlCon.Open();

            string str = "select COUNT(*) from SanPham dl ";
            if (status != -2)
            {
                str = str + "where dl.TinhTrang=" + status.ToString();
            }
            SqlCommand cmd = new SqlCommand(str, sqlCon);
            int soLuong = Convert.ToInt32(cmd.ExecuteScalar());
            sqlCon.Close();
            return soLuong;
        }
        public int slTrangDuLieu(int soLuongTrenTrang, int status)
        {
            int soLuong = this.soLuongDuLieu(status);
            if (soLuong > 0)
            {
                int soTrang = soLuong / soLuongTrenTrang;
                if (soLuong % soLuongTrenTrang != 0)
                {
                    soTrang++;
                }

                return soTrang;
            }
            return -1;
        }

        public int[] FilterMaDuLieu(int status, int startPoint, int size)
        {
            DataTable dt = new DataTable();
            int[] arrMaDuLieu = new int[size];
            string sql = "select dl.IDSanPham,ROW_NUMBER() over (order by dl.IDSanPham desc) as row_num from SanPham dl ";
            if (status != -2)
            {
                sql = sql + "where  dl.TinhTrang=" + status.ToString();
            }
            string sql1 = "select distinct(t0.IDSanPham) from (" + sql + ") as t0 where t0.row_num between " + (startPoint + 1) + " and " + (startPoint + size);
            sqlCon.Open();
            SqlCommand cmd = new SqlCommand(sql1, sqlCon);
            SqlDataReader dr = cmd.ExecuteReader();
            int i = 0;
            while (dr.Read())
            {
                arrMaDuLieu[i] = int.Parse(dr["IDSanPham"].ToString());
                i++;
            }

            sqlCon.Close();

            return arrMaDuLieu;
        }

        public List<SanPham> GetList(int status, int begin, int size)
        {
            int[] arrMaDuLieu = this.FilterMaDuLieu(status, begin, size);

            var result = (from dl in context.SanPhams
                          where arrMaDuLieu.Contains(dl.IDSanPham)
                          orderby dl.IDSanPham descending
                          select dl).ToList();
            return result;
        }

        public SanPham GetProductID(int id)
        {
            var q = (from sp in context.SanPhams
                     where sp.IDSanPham == id
                     select sp).FirstOrDefault();
            return q;
        }

        public List<SanPham> GetTopTenLatest()
        {
            var q = (from sp in context.SanPhams
                     where sp.TinhTrang == (int)SanPhamStatus.Active
                     orderby sp.IDSanPham descending
                     select sp).Take(10).ToList();
            return q;
        }

        public List<SanPham> GetTop5OfIDAcount(int idtaikhoan)
        {
            var q = (from sp in context.SanPhams
                     where sp.TinhTrang == (int)SanPhamStatus.Active && sp.IDTaiKhoan == idtaikhoan
                     orderby sp.IDSanPham descending
                     select sp).Take(5).ToList();
            return q;
        }

        public List<SanPham> GetTopTenLatestOfCategory(int parentID)
        {
            List<int> listID = new List<int>();
            listID.Add(parentID);//add category level1
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang==true).Select(u => u.IDDanhMuc).ToList()); //add category level2
            listID.AddRange(context.DanhMucs.Where(u => listID.Contains(u.IDDanhMucCha.Value) && u.TinhTrang == true).Select(u => u.IDDanhMuc).ToList()); //add category level3
            var result = context.SanPhams.Where(u => listID.Contains(u.IDDanhMuc) && u.TinhTrang == (int)SanPhamStatus.Active).Take(8).ToList();
            return result;
        }

        public List<SanPham> GetTopTenFeatured()
        {
            var q = (from sp in context.SanPhams
                     where sp.TinhTrang == (int)SanPhamStatus.Active
                     orderby sp.LuotContact descending
                     select sp).Take(10).ToList();
            return q;
        }

        
        public int slTrangDuLieuOfIDCatalogy(int soLuongTrenTrang, int soLuong)
        {
            if (soLuong > 0)
            {
                int soTrang = soLuong / soLuongTrenTrang;
                if (soLuong % soLuongTrenTrang != 0)
                {
                    soTrang++;
                }

                return soTrang;
            }
            return -1;
        }

        public List<int> FilterMaDanhMucOfIDCatalogy(int iddanhmuc)
        {
            DataTable dt = new DataTable();
            List<int> arrMaDuLieu = new List<int>() ;
            
            string sql = "select dl.IDDanhMuc from DanhMuc dl where dl.TinhTrang = 'True' and dl.IDDanhMucCha = " + iddanhmuc.ToString();
            sqlCon.Open();
            SqlCommand cmd = new SqlCommand(sql, sqlCon);
            SqlDataReader dr = cmd.ExecuteReader();
            arrMaDuLieu.Add(int.Parse(iddanhmuc.ToString()));
            while (dr.Read())
            {
                arrMaDuLieu.Add(int.Parse(dr["IDDanhMuc"].ToString()));
            }

            sqlCon.Close();

            return arrMaDuLieu;
        }

        public List<SanPham> GetListOfIDCatalogy(int iddanhmuc, int begin, int size)
        {
            List<int> arrMaDuLieu = this.FilterMaDanhMucOfIDCatalogy(iddanhmuc);
            var result = (from dl in context.SanPhams
                          where arrMaDuLieu.Contains(dl.IDDanhMuc) && dl.TinhTrang == (int)SanPhamStatus.Active
                          orderby dl.LuotContact descending
                          select dl).Skip(begin).Take(size).ToList();
            return result;
        }

        public List<SanPham> GetListOfIDCatalogyRelate(int iddanhmuc,int idsanpham ,int begin, int size)
        {
            List<int> arrMaDuLieu = this.FilterMaDanhMucOfIDCatalogy(iddanhmuc);
            var result = (from dl in context.SanPhams
                          where arrMaDuLieu.Contains(dl.IDDanhMuc) 
                          && dl.IDSanPham != idsanpham
                          && dl.TinhTrang == (int)SanPhamStatus.Active
                          orderby dl.LuotContact descending
                          select dl).Skip(begin).Take(size).ToList();
            return result;
        }

        public List<SanPham> GetListOfIDAccount(int id, int begin, int size)
        {
            var result = (from dl in context.SanPhams
                          where dl.IDTaiKhoan == id && dl.TinhTrang == (int)SanPhamStatus.Active
                          orderby dl.LuotContact descending
                          select dl).Skip(begin).Take(size).ToList();
            return result;
        }

        public int GetCountProductOfID(int id) {
            int q = context.SanPhams.Where(x => x.IDTaiKhoan == id && x.TinhTrang == (int)SanPhamStatus.Active).Count();
            return q;
        }

        public List<SanPham> Search(string strSearch, int IDCountry, int begin, int size)
        {
            List<SanPham> list = null;
            if (IDCountry > 0) // search theo ID Quoc Gia
            {
                list = (from dl in context.SanPhams
                        where dl.TinhTrang == (int)SanPhamStatus.Active && dl.ThongTinTaiKhoan.IDQuocGia == IDCountry &&
                          (dl.TenSanPham.Contains(strSearch) || dl.ChiTietSanPham.Contains(strSearch) || dl.TuKhoa.Contains(strSearch))
                        select dl).Skip(begin).Take(size).ToList();
            }
            else
            {
                list = (from dl in context.SanPhams
                        where dl.TinhTrang == (int)SanPhamStatus.Active &&
                          (dl.TenSanPham.Contains(strSearch) || dl.ChiTietSanPham.Contains(strSearch) || dl.TuKhoa.Contains(strSearch))
                        select dl).Skip(begin).Take(size).ToList();
            }
            return list;
        }

        public bool UpdateLuotContact(int idsanpham) {
            var q = (from dl in context.SanPhams
                     where dl.IDSanPham == idsanpham
                     select dl).FirstOrDefault();

            q.LuotContact = q.LuotContact + 1;
            try
            {
                context.SubmitChanges();
            }
            catch {
                return false;
            }
            return true;
        }
    }
}